﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using Microsoft.VisualBasic;
using modBusTest;
using Modbus.Device;
using System.Data.SQLite;
using System.Data.Common;

namespace ModbusTestTool
{
    class SqlCommunication
    {
        //for sql stuff
        SQLiteConnection conn;
        SQLiteCommand cmd;
        //this variables holds the name of the data table
        string table_data;


        public SqlCommunication(string inputFileName, modbusmap internalMap)
        {
            HandleCreatingSqlTables(inputFileName, internalMap);
        }


        private void HandleCreatingSqlTables(string inputFileName,modbusmap internalModbusMap )
        {
            if (!File.Exists(@"nk.db"))
                SQLiteConnection.CreateFile("nk.db");

            try
            {

                // conn = new SQLiteConnection("Data Source=nk.db");
                conn = new SQLiteConnection("Data Source=nk.db;Version=3;Compress=True;");

                var items = from n in internalModbusMap.storage select n.paramName;
                conn.Open();

                //this table has the key list
                cmd = new SQLiteCommand(conn);


                string hash = Crc32.ComputeHashString(inputFileName);

                //name of table we plan to put in the data base
                string table_key = inputFileName.Split('\\').Last().Split('.')[0] + "_key_" + hash;
                table_data = inputFileName.Split('\\').Last().Split('.')[0] + "_data_" + hash;


                //check to see if this table exists
                bool doesTableExist = DoesTableExist(table_key);





                //create table for the index
                if (!doesTableExist)
                {
                    cmd.CommandText = string.Format("CREATE TABLE {0} (Key_Name TEXT);", table_key);
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = string.Format("INSERT INTO {0} VALUES (\"Date\");", table_key);
                    cmd.ExecuteNonQuery();


                    //using a trasaction to put each key into the table
                    SQLiteTransaction temp = conn.BeginTransaction();
                    foreach (BaseRegister item in internalModbusMap.storage)
                    {
                        cmd.CommandText = string.Format("INSERT INTO {0} VALUES (\"{1}\");", table_key, item.paramName);
                        cmd.ExecuteNonQuery();
                    }
                    temp.Commit();

                    ((IDisposable)temp).Dispose();

                    //creating columns for all the different parameters
                    string[] keys = new string[internalModbusMap.storage.Count + 1];

                    keys[0] = "I0 TEXT";
                    for (int i = 1; i < keys.Length; i++)
                    {
                        keys[i] = "I" + i.ToString() + " REAL";
                    }

                    // do the sql statement to create the table

                    cmd.CommandText = string.Format("CREATE TABLE {0} ({1});", table_data, string.Join(",", keys));
                    cmd.ExecuteNonQuery();

                }
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }


            //cmd.CommandText = "CREATE TABLE maps (key CHAR, 


            MessageBox.Show("Created DataBase");
        }


        private bool DoesTableExist(string table_key)
        {
            bool doesTableExist = false;
            cmd.CommandText = "SELECT name FROM sqlite_master;";
            SQLiteDataReader dataRead = cmd.ExecuteReader();

            while (dataRead.Read())
            {
                if (dataRead.GetString(0) == table_key)
                {
                    doesTableExist = true;
                    break;
                }
            }

            dataRead.Close();
            return doesTableExist;
        }

        public void AddRow(IEnumerable<string> items)
        {
            cmd.CommandText = string.Format("INSERT INTO {0} VALUES ({1});", table_data, String.Format("\"{0}\",", DateAndTime.Now.ToString()) + string.Join(",", items));
            cmd.ExecuteNonQuery();
        }
   
    
    }
}
